/*
 * To change this template, choose Tools | Templates
 * and open the template in the editor.
 */
package com.yy.bugzillahelp.action;

import com.yy.bugzillahelp.MainFrame;
import com.yy.bugzillahelp.data.BugCommentData;
import com.yy.bugzillahelp.data.BugDetailData;
import com.yy.bugzillahelp.data.BugStatus;
import com.yy.bugzillahelp.tool.SQLTool;
import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.logging.Level;
import java.util.logging.Logger;

/**
 *
 * @author hualun-alan
 */
public class BugAction {

    private static final Logger logger = Logger.getLogger(BugAction.class.getName());

    public static int insertOne(Connection con, BugDetailData detailsData, String serverId) {
        String sql1 = "insert into bug_detail(bug_id,create_time,short_desc,modify_time,product,component,version,bug_status,resolution,priority,bug_severity,reporter,assigned_to,server_id)"
                + " values (?,?,?,?,?,?,?,?,?,?,?,?,?,?)";
        String sql2 = "insert into bug_comment(bug_id,comment_id,comment_when,comment_who,comment_text,server_id) values (?,?,?,?,?,?)";

        PreparedStatement ps1 = null;
        PreparedStatement ps2 = null;
        try {
            con.setAutoCommit(false);

            ps1 = con.prepareStatement(sql1);
            ps1.setString(1, detailsData.getBugId());
            ps1.setString(2, detailsData.getCreateTime());
            ps1.setString(3, detailsData.getShortDesc());
            ps1.setString(4, detailsData.getModifyTime());
            ps1.setString(5, detailsData.getProduct());
            ps1.setString(6, detailsData.getComponent());
            ps1.setString(7, detailsData.getVersion());
            ps1.setString(8, detailsData.getStatus());
            ps1.setString(9, detailsData.getResolution());
            ps1.setString(10, detailsData.getPriority());
            ps1.setString(11, detailsData.getSeverity());
            ps1.setString(12, detailsData.getReporter());
            ps1.setString(13, detailsData.getAssignedTo());
            ps1.setString(14, serverId);
            ps1.executeUpdate();

            ps2 = con.prepareStatement(sql2);
            for (BugCommentData commentData : detailsData.getDescDatas()) {
                ps2.setString(1, commentData.getBugId());
                ps2.setString(2, commentData.getCommentId());
                ps2.setString(3, commentData.getWhen());
                ps2.setString(4, commentData.getWho());
                ps2.setString(5, commentData.getText());
                ps2.setString(6, serverId);
                ps2.executeUpdate();
            }

            con.commit();
            return 1;
        } catch (SQLException ex) {
            Logger.getLogger(BugAction.class.getName()).log(Level.SEVERE, "sql1 = {0}", sql1);
            Logger.getLogger(BugAction.class.getName()).log(Level.SEVERE, "sql2 = {0}", sql2);
            Logger.getLogger(BugAction.class.getName()).log(Level.SEVERE, null, ex);
            return -1;
        } finally {
            SQLTool.closeStatement(ps1);
            SQLTool.closeStatement(ps2);
        }
    }

    public static int insertList(Connection con, ArrayList<BugDetailData> detailsDatas, String serverId) {
        String sql1 = "insert into bug_detail(bug_id,create_time,short_desc,modify_time,product,component,version,bug_status,resolution,priority,bug_severity,reporter,assigned_to,server_id)"
                + " values (?,?,?,?,?,?,?,?,?,?,?,?,?,?)";
        String sql2 = "insert into bug_comment(bug_id,comment_id,comment_when,comment_who,comment_text,server_id) values (?,?,?,?,?,?)";

        PreparedStatement ps1 = null;
        PreparedStatement ps2 = null;
        try {
            con.setAutoCommit(false);

            ps1 = con.prepareStatement(sql1);
            ps2 = con.prepareStatement(sql2);

            for (BugDetailData detailsData : detailsDatas) {
                MainFrame.getFrame().setStatus("insert bug details to db - " + detailsData.getBugId() + " - " + detailsData.getShortDesc());

                ps1.setString(1, detailsData.getBugId());
                ps1.setString(2, detailsData.getCreateTime());
                ps1.setString(3, detailsData.getShortDesc());
                ps1.setString(4, detailsData.getModifyTime());
                ps1.setString(5, detailsData.getProduct());
                ps1.setString(6, detailsData.getComponent());
                ps1.setString(7, detailsData.getVersion());
                ps1.setString(8, detailsData.getStatus());
                ps1.setString(9, detailsData.getResolution());
                ps1.setString(10, detailsData.getPriority());
                ps1.setString(11, detailsData.getSeverity());
                ps1.setString(12, detailsData.getReporter());
                ps1.setString(13, detailsData.getAssignedTo());
                ps1.setString(14, serverId);
                ps1.executeUpdate();

                for (BugCommentData commentData : detailsData.getDescDatas()) {
                    ps2.setString(1, commentData.getBugId());
                    ps2.setString(2, commentData.getCommentId());
                    ps2.setString(3, commentData.getWhen());
                    ps2.setString(4, commentData.getWho());
                    ps2.setString(5, commentData.getText());
                    ps2.setString(6, serverId);
                    ps2.executeUpdate();
                }
            }

            con.commit();
            return 1;
        } catch (SQLException ex) {
            Logger.getLogger(BugAction.class.getName()).log(Level.SEVERE, "sql1 = {0}", sql1);
            Logger.getLogger(BugAction.class.getName()).log(Level.SEVERE, "sql2 = {0}", sql2);
            Logger.getLogger(BugAction.class.getName()).log(Level.SEVERE, null, ex);
            return -1;
        } finally {
            SQLTool.closeStatement(ps1);
            SQLTool.closeStatement(ps2);
        }
    }

    public static int updateOne(Connection con, BugDetailData newData, String serverId) {
        BugDetailData oldData = searchById(con, newData.getBugId(), serverId);

        if (oldData.getBugId() == null || oldData.getBugId().trim().isEmpty()) {
            return insertOne(con, newData, serverId);
        } else {
            String sql1 = "update bug_detail set short_desc=?,modify_time=?,product=?,component=?,version=?,"
                    + " bug_status=?,resolution=?,priority=?,bug_severity=?,assigned_to=?"
                    + " where bug_id=? and server_id=?";
            String sql2 = "select * from bug_comment where comment_id=? and bug_id=? and server_id=? ";
            String sql3 = "insert into bug_comment(bug_id,comment_id,comment_when,comment_who,comment_text,server_id) values (?,?,?,?,?,?)";

            PreparedStatement ps1 = null;
            PreparedStatement ps2 = null;
            PreparedStatement ps3 = null;
            try {
                ps1 = con.prepareStatement(sql1);
                ps2 = con.prepareStatement(sql2);
                ps3 = con.prepareStatement(sql3);

                ps1.setString(1, newData.getShortDesc());
                ps1.setString(2, newData.getModifyTime());
                ps1.setString(3, newData.getProduct());
                ps1.setString(4, newData.getComponent());
                ps1.setString(5, newData.getVersion());
                ps1.setString(6, newData.getStatus());
                ps1.setString(7, newData.getResolution());
                ps1.setString(8, newData.getPriority());
                ps1.setString(9, newData.getSeverity());
                ps1.setString(10, newData.getAssignedTo());
                ps1.setString(11, newData.getBugId());
                ps1.setString(12, serverId);
                ps1.executeUpdate();

                ps2.setString(2, newData.getBugId());
                ps2.setString(3, serverId);

                ps3.setString(6, serverId);
                for (BugCommentData commentData : newData.getDescDatas()) {
                    ps2.setString(1, commentData.getCommentId());
                    ResultSet rs2 = ps2.executeQuery();
                    if (rs2.next()) {
                        //已经存在，则不需要做更新，因为Comment是不能修改的
                    } else {
                        ps3.setString(1, commentData.getBugId());
                        ps3.setString(2, commentData.getCommentId());
                        ps3.setString(3, commentData.getWhen());
                        ps3.setString(4, commentData.getWho());
                        ps3.setString(5, commentData.getText());
                        ps3.executeUpdate();
                    }
                }

                return 1;
            } catch (SQLException ex) {
                logger.log(Level.INFO, "sql = {0}", sql1);
                Logger.getLogger(BugAction.class.getName()).log(Level.SEVERE, null, ex);
                return -1;
            } finally {
                SQLTool.closeStatement(ps1);
                SQLTool.closeStatement(ps2);
                SQLTool.closeStatement(ps3);
            }
        }
    }

    public static int updateList(Connection con, ArrayList<BugDetailData> detailsDatas, String serverId) {
        String sql1 = "update bug_detail set short_desc=?,modify_time=?,product=?,component=?,version=?,"
                + " bug_status=?,resolution=?,priority=?,bug_severity=?,assigned_to=?"
                + " where bug_id=? and server_id=?";
        String sql2 = "select * from bug_comment where comment_id=? and bug_id=? and server_id=? ";
        String sql3 = "insert into bug_comment(bug_id,comment_id,comment_when,comment_who,comment_text,server_id) values (?,?,?,?,?,?)";

        PreparedStatement ps1 = null;
        PreparedStatement ps2 = null;
        PreparedStatement ps3 = null;

        try {
            ps1 = con.prepareStatement(sql1);
            ps2 = con.prepareStatement(sql2);
            ps3 = con.prepareStatement(sql3);

            for (BugDetailData newData : detailsDatas) {
                BugDetailData oldData = searchById(con, newData.getBugId(), serverId);

                if (oldData.getBugId() == null || oldData.getBugId().trim().isEmpty()) {
                    insertOne(con, newData, serverId);
                } else {
                    ps1.setString(1, newData.getShortDesc());
                    ps1.setString(2, newData.getModifyTime());
                    ps1.setString(3, newData.getProduct());
                    ps1.setString(4, newData.getComponent());
                    ps1.setString(5, newData.getVersion());
                    ps1.setString(6, newData.getStatus());
                    ps1.setString(7, newData.getResolution());
                    ps1.setString(8, newData.getPriority());
                    ps1.setString(9, newData.getSeverity());
                    ps1.setString(10, newData.getAssignedTo());
                    ps1.setString(11, newData.getBugId());
                    ps1.setString(12, serverId);
                    ps1.executeUpdate();

                    ps2.setString(2, newData.getBugId());
                    ps2.setString(3, serverId);

                    ps3.setString(6, serverId);
                    for (BugCommentData commentData : newData.getDescDatas()) {
                        ps2.setString(1, commentData.getCommentId());
                        ResultSet rs2 = ps2.executeQuery();
                        if (rs2.next()) {
                            //已经存在，则不需要做更新，因为Comment是不能修改的
                        } else {
                            ps3.setString(1, commentData.getBugId());
                            ps3.setString(2, commentData.getCommentId());
                            ps3.setString(3, commentData.getWhen());
                            ps3.setString(4, commentData.getWho());
                            ps3.setString(5, commentData.getText());
                            ps3.executeUpdate();
                        }
                    }
                }
            }
            return 1;
        } catch (SQLException ex) {
            logger.log(Level.INFO, "sql = {0}", sql1);
            Logger.getLogger(BugAction.class.getName()).log(Level.SEVERE, null, ex);
            return -1;
        } finally {
            SQLTool.closeStatement(ps1);
            SQLTool.closeStatement(ps2);
            SQLTool.closeStatement(ps3);
        }
    }

    /**
     *
     * @param con
     * @param id
     * @return BugDetailData, null if occurs exception
     */
    public static BugDetailData searchById(Connection con, String id, String serverId) {
        BugDetailData detailsData = new BugDetailData();

        String sql1 = "select * from bug_detail where bug_id = ? and server_id = ?";
        String sql2 = "select * from bug_comment where bug_id = ? and server_id = ?";
        PreparedStatement ps1 = null;
        PreparedStatement ps2 = null;
        try {
            ps1 = con.prepareStatement(sql1);
            ps1.setString(1, id);
            ps1.setString(2, serverId);
            ResultSet rs1 = ps1.executeQuery();
            if (rs1.next()) {
                getDetailData(detailsData, rs1);
                ps2 = con.prepareStatement(sql2);
                ps2.setString(1, id);
                ps2.setString(2, serverId);
                ResultSet rs2 = ps2.executeQuery();
                while (rs2.next()) {
                    BugCommentData descData = new BugCommentData();
                    getCommentData(descData, rs2);
                    detailsData.addDescData(descData);
                }
            }
        } catch (SQLException ex) {
            Logger.getLogger(BugAction.class.getName()).log(Level.SEVERE, "sql1 = {0}", sql1);
            Logger.getLogger(BugAction.class.getName()).log(Level.SEVERE, "sql2 = {0}", sql2);
            Logger.getLogger(BugAction.class.getName()).log(Level.SEVERE, null, ex);
            return null;
        } finally {
            SQLTool.closeStatement(ps1);
            SQLTool.closeStatement(ps2);
        }
        return detailsData;
    }

    /**
     * search all bugs of a server
     *
     * @param con
     * @param id
     * @return a list of BugDetailData, null if occurs exception
     */
    public static ArrayList<BugDetailData> searchAll(Connection con, String serverId) {
        ArrayList<BugDetailData> list = new ArrayList<BugDetailData>();

        String sql = "select * from bug_detail where server_id = ? order by cast(bug_id as int) desc";
        PreparedStatement ps = null;
        try {
            ps = con.prepareStatement(sql);
            ps.setString(1, serverId);
            ResultSet rs = ps.executeQuery();
            while (rs.next()) {
                BugDetailData data = new BugDetailData();
                getDetailData(data, rs);
                list.add(data);
            }
        } catch (SQLException ex) {
            Logger.getLogger(BugAction.class.getName()).log(Level.SEVERE, "sql = {0}", sql);
            Logger.getLogger(BugAction.class.getName()).log(Level.SEVERE, null, ex);
            return null;
        } finally {
            SQLTool.closeStatement(ps);
        }

        return list;
    }

    /**
     * search bugs by status from a server
     *
     * @param con
     * @param id
     * @param statuses
     * @return a list of BugDetailData, null if occurs exception
     */
    public static ArrayList<BugDetailData> searchByStatus(Connection con, String serverId, BugStatus[] statuses) {
        ArrayList<BugDetailData> list = new ArrayList<BugDetailData>();

        StringBuilder statusWhere = new StringBuilder();
        if (statuses != null) {
            for (BugStatus bugStatus : statuses) {
                if (bugStatus != null) {
                    statusWhere.append("'").append(bugStatus.name()).append("',");
                }
            }
        }

        String sql = "select * from bug_detail where server_id = ? ";
        if (statusWhere.length() > 0) {
            sql += " and bug_status in (" + statusWhere.substring(0, statusWhere.length() - 1) + ")";
        }
        sql += " order by cast(bug_id as int) desc";


        PreparedStatement ps = null;
        try {
            ps = con.prepareStatement(sql);
            ps.setString(1, serverId);
            ResultSet rs = ps.executeQuery();
            while (rs.next()) {
                BugDetailData data = new BugDetailData();
                getDetailData(data, rs);
                list.add(data);
            }
        } catch (SQLException ex) {
            Logger.getLogger(BugAction.class.getName()).log(Level.SEVERE, "sql = {0}", sql);
            Logger.getLogger(BugAction.class.getName()).log(Level.SEVERE, null, ex);
            return null;
        } finally {
            SQLTool.closeStatement(ps);
        }

        return list;
    }

    private static void getDetailData(BugDetailData data, ResultSet rs) throws SQLException {
        data.setBugId(rs.getString("bug_id"));
        data.setCreateTime(rs.getString("create_time"));
        data.setShortDesc(rs.getString("short_desc"));
        data.setModifyTime(rs.getString("modify_time"));
        data.setProduct(rs.getString("product"));
        data.setComponent(rs.getString("component"));
        data.setVersion(rs.getString("version"));
        data.setStatus(rs.getString("bug_status"));
        data.setResolution(rs.getString("resolution"));
        data.setPriority(rs.getString("priority"));
        data.setSeverity(rs.getString("bug_severity"));
        data.setReporter(rs.getString("reporter"));
        data.setAssignedTo(rs.getString("assigned_to"));
    }

    private static void getCommentData(BugCommentData descData, ResultSet rs2) throws SQLException {
        descData.setBugId(rs2.getString("bug_id"));
        descData.setCommentId(rs2.getString("comment_id"));
        descData.setWhen(rs2.getString("comment_when"));
        descData.setWho(rs2.getString("comment_who"));
        descData.setText(rs2.getString("comment_text"));
    }
}
